package pe.edutec.app.layer.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import pe.edutec.app.dto.ProfesorCursoDto;
import pe.edutec.app.layer.dao.interfaces.ReporteDao;

/**
 *
 * @author Gustavo Coronel
 */
public class ReporteDaoImpl implements ReporteDao{

	@Override
	public List<ProfesorCursoDto> reportePagos(String ciclo) {
		Connection cn = null;
		List<ProfesorCursoDto> lista = new ArrayList<ProfesorCursoDto>();
		try {
			cn = AccesoDB.getConnection();
			String sql = "select "
							+ "cp.IdCursoProg as id, "
							+ "cp.IdCiclo as ciclo, "
							+ "c.IdCurso as codCurso, "
							+ "c.NomCurso as  nomCurso, "
							+ "p.IdProfesor as codProfesor, "
							+ "p.NomProfesor as nomProfesor, "
							+ "c.horas as horas, "
							+ "cp.Horario as horario, "
							+ "p.tarifa as tarifa, "
							+ "c.horas * p.tarifa as importe "
							+ "from dbo.Curso c "
							+ "join dbo.CursoProgramado cp on c.IdCurso = cp.IdCurso "
							+ "join dbo.Profesor p on cp.IdProfesor = p.IdProfesor "
							+ "where cp.IdCiclo = ? "
							+ "order by p.IdProfesor, cp.IdCursoProg";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, ciclo);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProfesorCursoDto dto = new ProfesorCursoDto();
				dto.setId(rs.getInt("id"));
				dto.setCiclo(rs.getString("ciclo"));
				dto.setCodCurso(rs.getString("codCurso"));
				dto.setNomCurso(rs.getString("nomCurso"));
				dto.setCodProfesor(rs.getString("codProfesor"));
				dto.setNomProfesor(rs.getString("nomProfesor"));
				dto.setHoras(rs.getInt("horas"));
				dto.setHorario(rs.getString("horario"));
				dto.setTarifa(rs.getDouble("tarifa"));
				dto.setImporte(rs.getDouble("importe"));
				lista.add(dto);
			}
			rs.close();
			pstm.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			throw new RuntimeException("Error al consultar tabla de alumnos.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
		return lista;
	}

	@Override
	public List<Map<String, ?>> reporteCursosProgramados(String periodo) {
		Connection cn = null;
		List<Map<String, ?>> lista = new ArrayList<Map<String, ?>>();
		try {
			cn = AccesoDB.getConnection();
			String sql = "select "
							+ "cp.IdCiclo as ciclo, "
							+ "cp.IdCursoProg as id, "
							+ "c.IdCurso as codCurso, "
							+ "c.NomCurso as nomCurso, "
							+ "cp.Horario as horario, "
							+ "CAST(cp.vacantes as varchar) + '/' + "
							+ "CAST(cp.matriculados as varchar) as vacantes, "
							+ "cp.Horario as horario, "
							+ "isnull(p.ApeProfesor + ', ' + p.NomProfesor,'None') as profesor "
							+ "from dbo.Curso c "
							+ "join dbo.CursoProgramado cp "
							+ "on c.IdCurso = cp.IdCurso "
							+ "left join Profesor p "
							+ "on cp.IdProfesor = p.IdProfesor "
							+ "where cp.IdCiclo like ? "
							+ "order by cp.IdCiclo, cp.IdCursoProg";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, periodo + "%");
			ResultSet rs = pstm.executeQuery();
			lista = JdbcUtil.resultSetToArrayList(rs);
			rs.close();
			pstm.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			throw new RuntimeException("Error al consultar tabla de alumnos.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
		return lista;
	}
	
}
